package edu.cmu.cs.webapp.ioms.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.eclipse.rwt.RWT;

import edu.cmu.cs.webapp.ioms.dao.DataAccessException;
import edu.cmu.cs.webapp.ioms.models.User;

public class DAO {
	protected List<Connection> connectionPool = new ArrayList<Connection>();
	protected String jdbcDriver;
	protected String jdbcURL;
	static boolean tableCreated = false;

	public DAO() {
		// TODO Auto-generated constructor stub
		this.jdbcDriver = "com.mysql.jdbc.Driver";
		this.jdbcURL = "jdbc:mysql://localhost:3306/webapp";
		if (!tableCreated) {
			try {
				createTable();
				tableCreated = true;
			} catch (Exception e) {
				// e.printStackTrace();
			}
		}
	}

	protected synchronized Connection getConnection() throws Exception {
		if (connectionPool.size() > 0) {
			return connectionPool.remove(connectionPool.size() - 1);
		}

		try {
			Class.forName(jdbcDriver);
		} catch (ClassNotFoundException e) {
			throw new Exception(e);
		}

		try {
			return DriverManager.getConnection(jdbcURL);
		} catch (SQLException e) {
			throw new Exception(e);
		}
	}

	protected synchronized void releaseConnection(Connection con) {
		connectionPool.add(con);
	}

	private void createTable() throws Exception {
		Connection con = null;
		try {
			con = getConnection();
			Statement stmt = con.createStatement();

			stmt
					.executeUpdate("CREATE TABLE user (user_id int(20) auto_increment,"
							+ "name varchar(255), "
							+ "login varchar(255), "
							+ "password varchar(255), "
							+ "role varchar(255), "
							+ "department varchar(255), "
							+ "PRIMARY KEY(user_id)" + ") TYPE=INNODB");

			stmt
					.executeUpdate("CREATE TABLE client (client_id int(20) auto_increment,"
							+ "lname varchar(255), "
							+ "fname varchar(255), "
							+ "email varchar(255), "
							+ "phone varchar(255), "
							+ "company varchar(255), "
							+ "address varchar(255), "
							+ "city varchar(255), "
							+ "state varchar(255), "
							+ "country varchar(255), "
							+ "PRIMARY KEY(client_id)" + ") TYPE=INNODB");

			stmt
					.executeUpdate("CREATE TABLE po (order_id int(20) auto_increment,"
							+ "client_id int(20), "
							+ "order_date datetime, "
							+ "shipping_address varchar(255), "
							+ "shipping_city varchar(255), "
							+ "shipping_state varchar(255), "
							+ "shipping_country varchar(255),"
							+ "expected_shipping_date datetime, "
							+ "status varchar(255)," +
							// "foreign key(client_id) references client(client_id) ON DELETE CASCADE,"
							// +
							"PRIMARY KEY(order_id)) TYPE=INNODB");

			stmt
					.executeUpdate("CREATE TABLE product (prod_id int(20) auto_increment,"
							+ "prod_name varchar(255), "
							+ "prod_description varchar(255), "
							+ "price double, "
							+ "PRIMARY KEY(prod_id)"
							+ ") TYPE=INNODB");

			stmt
					.executeUpdate("CREATE TABLE ingredient (ing_id int(20) auto_increment,"
							+ "name varchar(255), "
							+ "inventory double, "
							+ "unit_cost double, "
							+ "remark varchar(255), "
							+ "lastupdate datetime, "
							+ "updateby varchar(255), "
							+ "PRIMARY KEY(ing_id)"
							+ ") TYPE=INNODB");

			stmt
					.executeUpdate("CREATE TABLE item (item_id int(20) auto_increment,"
							+ "prod_id int(20), "
							+ "order_id int(20), "
							+ "quantity double, "
							+ "unit varchar(255), "
							+ "remark varchar(255), "
							+ "PRIMARY KEY(item_id)"
							+ ") TYPE=INNODB");

			stmt
					.executeUpdate("CREATE TABLE formula (form_id int(20) auto_increment,"
							+ "prod_id int(20), "
							+ "ing_id int(20), "
							+ "ing_std_quantity double, "
							+ "PRIMARY KEY(form_id)" + ") TYPE=INNODB");

			stmt
					.executeUpdate("CREATE TABLE log (log_id int(20) auto_increment,"
							+ "log_msg varchar(1500), "
							+ "log_type varchar(255), "
							+ "user varchar(255), "
							+ "time datetime, "
							+ "PRIMARY KEY(log_id)"
							+ ") TYPE=INNODB");

			stmt.close();
			releaseConnection(con);

		} catch (SQLException e) {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e2) { /* ignore */
			}
			throw new Exception(e);
		}
	}

	protected void log(String msg, String type) throws DataAccessException {
		Object obj = RWT.getSessionStore().getAttribute("uid");
		User usr = null;
		if (obj != null && obj instanceof User) {
			usr = (User) obj;
		}
		
		String u = usr.getLogin() + "(" + usr.getName() + ")";
		
		Connection con = null;
		try {
			con = getConnection();

			PreparedStatement pstmt = con
					.prepareStatement("insert into log(log_msg, log_type, user, time) "
							+ "values (? ,?, ?, ?)");

			pstmt.setString(1, msg);
			pstmt.setString(2, type);
			pstmt.setString(3, u);
			pstmt.setDate(4, new Date(System.currentTimeMillis()));

			pstmt.executeUpdate();
			pstmt.close();
			con.close();
		} catch (Exception e) {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e2) { /* ignore */
			}
			throw new DataAccessException(e);
		}		
	}
}
